USE [DSRSystem_Temp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPrepareSnapshotData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spPrepareSnapshotData]
GO


CREATE PROCEDURE [dbo].[spPrepareSnapshotData]
@ChangedSince datetime
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @transaction_isolation_level SMALLINT;

	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    print('initial transaction_isolation_level: ' + STR(@transaction_isolation_level))


	EXECUTE dbo.spPrepareTables


	DECLARE @Log TABLE
	(
		Description nvarchar(max) NOT NULL,
		ElapsedTime int NOT NULL,
		RowsAffected int NOT NULL
	)

	declare @totalTime datetime
	declare @startTime datetime
	declare @endTime datetime
	declare @elapsedTime int
	declare @rowsAffected int

	select @totalTime = getdate()
	select @startTime = getdate()

	declare @LastSyncDate datetime
	declare @SyncAll bit = 1
	declare @LastUpdate datetime
	declare @DateTimeMinValue datetime = '1753-01-01'


	TRUNCATE TABLE FinalizedCompanies
	INSERT INTO FinalizedCompanies
	EXECUTE [DSRSystem].[dbo].[spGetGovernanceFinalizedCompanies] @ChangedSince

	set @rowsAffected = @@ROWCOUNT

	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	insert into @Log (Description, ElapsedTime, RowsAffected)
	values ('Extract Finalized Companies', @elapsedTime, @rowsAffected)
	select @startTime = getdate()


	SELECT TOP(1) @LastUpdate = LastUpdate FROM [dbo].[SyncLog] ORDER BY LastSyncDate DESC

	--IF (@ChangedSince = @DateTimeMinValue) SET @LastUpdate = NULL


	DECLARE @SelectedCompaniesCount int = 0

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	IF @LastUpdate IS NULL 
		BEGIN
			INSERT INTO @SelectedCompanies
			SELECT CompanyId AS Id FROM FinalizedCompanies

			set @SelectedCompaniesCount = @@ROWCOUNT

			insert into @Log (Description, ElapsedTime, RowsAffected)
			values (str(@SelectedCompaniesCount) + ' finalized companies changed since the beginning of time', 0, @SelectedCompaniesCount)
		END
	ELSE
		BEGIN
			INSERT INTO @SelectedCompanies
			SELECT CompanyId AS Id FROM FinalizedCompanies 
			WHERE LastFinalUpdate > @LastUpdate
			OR LastCentralizedResearchUpdate > @LastUpdate

			set @SelectedCompaniesCount = @@ROWCOUNT

			insert into @Log (Description, ElapsedTime, RowsAffected)
			values (str(@SelectedCompaniesCount) + ' finalized companies changed since ' +  CONVERT(nvarchar(30), @LastUpdate, 126), 0, @SelectedCompaniesCount)
		END


	IF (@SelectedCompaniesCount) > 0
	BEGIN

		-- STEP 1 -> Extract data
		
		DECLARE @CompanyIds nvarchar(max)
		DECLARE @XML XML
		
		SET @XML = (SELECT * FROM @SelectedCompanies FOR XML RAW (''), ROOT('CompanyIds'), ELEMENTS, TYPE)
	    SET @CompanyIds = CONVERT(NVARCHAR(MAX), @XML, 1)



		SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
		BEGIN TRANSACTION;

		SELECT @transaction_isolation_level = transaction_isolation_level
		FROM sys.dm_exec_requests
		WHERE session_id = @@spid;

		print('after SET TRANSACTION ISOLATION LEVEL SNAPSHOT' + STR(@transaction_isolation_level))



		TRUNCATE TABLE [dbo].[IndicatorTemplateTickboxes]
		INSERT INTO [dbo].[IndicatorTemplateTickboxes]
		EXECUTE [DSRSystem].[dbo].[spGetIndicatorTemplateTickboxes]

		TRUNCATE TABLE [dbo].[IndicatorTemplateAnswerCategories]
		INSERT INTO [dbo].[IndicatorTemplateAnswerCategories]
		EXECUTE [DSRSystem].[dbo].[spGetIndicatorTemplateAnswerCategories]

		TRUNCATE TABLE [dbo].[IndicatorTemplates]
		INSERT INTO [dbo].[IndicatorTemplates]
		EXECUTE [DSRSystem].[dbo].[spGetIndicatorTemplates]

		set @rowsAffected = @@ROWCOUNT

		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Extract Indicator Templates', @elapsedTime, @rowsAffected)
		select @startTime = getdate()



		EXECUTE @rowsAffected = dbo.spExtractFinalizedIndicators @CompanyIds

		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Extract Indicators', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		EXECUTE @rowsAffected = dbo.spExtractFinalizedSources @CompanyIds

		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Extract Sources', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		COMMIT TRANSACTION;


		-- STEP 2 -> Load data

		SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

		BEGIN TRANSACTION;

		DELETE FinalizedIndicators FROM dbo.FinalizedIndicators AS FinalizedIndicators
		LEFT OUTER JOIN dbo.FinalizedCompanies AS FinalizedCompanies ON FinalizedIndicators.CompanyId = FinalizedCompanies.CompanyId
		WHERE  (FinalizedCompanies.CompanyId IS NULL)   
	
		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete indicators that do not belong to current companies', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		IF (@LastUpdate IS NULL) BEGIN
			TRUNCATE TABLE [dbo].[FinalizedIndicators] END 
		ELSE BEGIN
			DELETE FinalizedIndicators FROM dbo.FinalizedIndicators AS FinalizedIndicators
			INNER JOIN @SelectedCompanies AS SelectedCompanies ON FinalizedIndicators.CompanyId = SelectedCompanies.Id
		END

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete indicators that are going to be updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		INSERT INTO [dbo].[FinalizedIndicators]
		SELECT * FROM [dbo].[StageFinalizedIndicators]

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Indicators have been updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		DELETE FinalizedSources FROM dbo.FinalizedSources AS FinalizedSources
		LEFT OUTER JOIN dbo.FinalizedCompanies AS FinalizedCompanies ON FinalizedSources.CompanyId = FinalizedCompanies.CompanyId
		WHERE  (FinalizedCompanies.CompanyId IS NULL)   
	
		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete sources that do not belong to current companies', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		IF (@LastUpdate IS NULL) BEGIN
			TRUNCATE TABLE [dbo].[FinalizedSources] END 
		ELSE BEGIN
			DELETE FinalizedSources FROM dbo.FinalizedSources AS FinalizedSources
			INNER JOIN @SelectedCompanies AS SelectedCompanies ON FinalizedSources.CompanyId = SelectedCompanies.Id
		END

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Delete sources that are going to be updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		INSERT INTO [dbo].[FinalizedSources]
		SELECT * FROM [dbo].[StageFinalizedSources]

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('Sources have been updated', @elapsedTime, @rowsAffected)

		COMMIT TRANSACTION;
	
	END





	declare @LastFinalUpdate datetime
	declare @LastCentralizedResearchUpdate datetime

	select @LastFinalUpdate = max(LastFinalUpdate) from [dbo].[FinalizedCompanies]
	select @LastCentralizedResearchUpdate = max(LastCentralizedResearchUpdate) from [dbo].[FinalizedCompanies]

	set @LastUpdate = @LastFinalUpdate
	if @LastCentralizedResearchUpdate > @LastUpdate set @LastUpdate = @LastCentralizedResearchUpdate


	select @endTime = getdate()
	select @elapsedTime = datediff(second, @totalTime, @endTime)
	print 'SnapshotData completed successfully => Total elapsed time:' +  str(@elapsedTime)
	insert into @Log (Description, ElapsedTime, RowsAffected)
	values ('SnapshotData completed successfully', @elapsedTime, 0)


	insert into [dbo].[SyncLog] ([LastSyncDate], [ElapsedTime], [LastUpdate])
	values (GETUTCDATE(), @elapsedTime, @LastUpdate)

	select * from @Log

END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetIndicatorsByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetIndicatorsByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetIndicatorsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT FinalizedIndicators.[Id]
		  ,[CompanyId]
		  ,[Name]
		  ,[Number]
		  ,[Description]
		  ,[Score]
		  ,[Outlook]
		  ,[Tickedboxes]
		  ,[Comments]
		  ,[Sources]
	FROM [dbo].[FinalizedIndicators] AS FinalizedIndicators
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = FinalizedIndicators.CompanyId
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetSourcesByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetSourcesByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetSourcesByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT FinalizedSources.Id
		  ,[CompanyId]
		  ,[Code]
		  ,[Text]
		  ,[IndicatorsNumbers]
	FROM [dbo].[FinalizedSources] AS FinalizedSources
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = FinalizedSources.CompanyId

END
GO
